# import  uiautomation
import pandas as pd
import datetime

# class person:
#     def __init__(self):
#         print("初始化流程")
#
#     def  tudo(self):
#         print("做")
#         i=0
#         while(i<5):
#             print(i)
#             i+=1
#     def uiauto(self):
#         uiautomation.PaneControl()
#
#
# #xiao=person()
# #xiao.tudo()
a = ()
b = []
c = {"keys": [1, 2, [3, 4]]}
d = (1,)

print(type(a))
print(type(b))
b.append(123)
print(b)
print(type(c))
c["keys1"] = "asdfg"
c["keys2"] = b
print(c)
print(type(d))

e = [1, 2, 3, 3, 4, 4, 5]
f = set(e)
print(f)

d = [1, 2, 3, 3, 4, 4, 5]
d.append(6)
print(d)

df1 = pd.DataFrame({
    "name": ["小明", "小红", "小孙", "王小", "关宇", "刘蓓", "张菲"],
    "age": [20, 18, 27, 20, 28, 18, 25],
    "sex": ["男", "女", "男", "男", "男", "女", "女"],
    "score": [669, 570, 642, 590, 601, 619, 701],
    "address": ["北京", "深圳", "广州", "武汉", "深圳", "广州", "长沙"]
})
print(df1.values.tolist())
df1.loc[1, 'name'] = '小吕'
print(df1)
print(df1['name'].iloc[1])

date = df1[df1['name'].isin(['小孙'])]
print(date['name'].item())
print('包含这个字段的属于海外地区，\n保障方案为‘海外员工方案')
headList = df1.columns.values.tolist()
当前所选工厂 = '1003'
if ('names' not in headList):
    msg = '{}工厂没有维护评估类'.format(当前所选工厂)
    # Log.Info(self, msg)
    print(f"表头：{msg}")

list1 = [1, 2, 3, 4, 5]
list2 = ['a', 'b', 'c', 'd']

for item1, item2 in zip(list1, list2):
    print(item1, item2)

import pandas as pd

# 创建一个包含重复行的DataFrame
df = pd.DataFrame({'A': [1, 2, 2], 'B': ['a', 'b', 'b'], 'C': ['a', 'b', 'a']})
print("原始DataFrame:")
print(df)

# 删除重复行并生成新的DataFrame
new_df = df.drop_duplicates()
print("\n删除重复行后的DataFrame:")
print(new_df)

import pandas as pd

# 创建一个示例DataFrame
df = pd.DataFrame({
    'A': ['foo', 'bar', 'foo1', 'bar', 'foo', 'bar', 'foo', 'foo'],
    'B': ['one', 'one', 'two', 'three', 'two', 'two', 'one', 'three'],
    'C': [1, 2, 3, 4, 5, 6, 7, 8],
    'D': [9, 10, 11, 12, 13, 14, 15, 16]
})
print(df)
# 将相同的行按照列'A'进行分组，然后对其余列进行求和
df_summed = df.groupby('A', as_index=True).sum()

print(df_summed)
df_summed = df.groupby('A', as_index=False)[['C', 'D']].sum()

print(df_summed)

import pandas as pd

# 示例DataFrame
df = pd.DataFrame({
    'A': ['foo', 'bar', 'foo', 'bar', 'foo', 'bar', 'foo', 'foo'],
    'B': ['one', 'one', 'two', 'three', 'two', 'two', 'one', 'three'],
    'C': [1, 2, 3, 4, 5, 6, 7, 8],
    'D': [9, 10, 11, 12, 13, 14, 15, 16]
})

# 对'A'列进行分组，对'C'列求和，在'D'列中选择最小值
result = df.groupby('A').agg({'C': 'sum', 'D': 'min'})

print(result)

import pandas as pd

# 创建示例DataFrame
df = pd.DataFrame({
    'A': ['foo', 'bar', 'foo', 'bar', 'foo', 'bar', 'foo', 'foo'],
    'B': ['one', 'one', 'two', 'three', 'two', 'two', 'one', 'three'],
    'C': [1, 2, 3, 4, 5, 6, 7, 8],
    'D': [9, 10, 11, 12, 13, 14, 15, 16]
})
print(df)
# 对'A'列进行分组，并对'C'列求和，同时保留'B'列的第一个条目
result = df.groupby('A', as_index=False).agg({'C': 'sum', 'B': 'first', 'D': 'first', 'A': 'first'})

# 或者直接选择列
# result = df.groupby('A', as_index=False).agg({'C': 'sum', 'D': lambda x: x.iloc[0]})

print(result)

import pandas as pd

# 创建示例 DataFrame
data1 = {
    'key': ['A', 'B', 'C', 'D'],
    'value1': [1, 2, 3, 4]
}
df1 = pd.DataFrame(data1)
print(df1)
data2 = {
    'key': ['A', 'C'],
    'value2': [10, 20]
}
df2 = pd.DataFrame(data2)
print(df2)
# 根据 'key' 列合并两个 DataFrame
merged_df = pd.merge(df1, df2, on='key', how='left')
print(merged_df)
'''
# 添加相同 'key' 列的值，并替换原来的值
merged_df['new_value1'] = merged_df['value1'] + merged_df['value2']
# merged_df['value1'] = merged_df['value1'] + merged_df['value2']

print(merged_df)
# 将 NaN 替换回原来的值
merged_df['value1'] = merged_df['new_value1'].fillna(merged_df['value1'])

# 删除新添加的列
merged_df = merged_df.drop(columns=['new_value1'])

# 显示合并后的结果
print(merged_df)


import pandas as pd

# 创建示例数据
data = {
'A': ['foo', 'bar', 'foo', 'bar','do'],
'B': [1, 2, 3, 4,5],
'C': [5, 6, 7, 8,9]
}

df = pd.DataFrame(data)
print(df)
# 使用groupby和transform对列'B'进行求和，同时保留列'C'的值
df['B'] = df.groupby('A')['B'].transform('sum')
print(df)
# 如果需要，可以删除重复的'A'列值
df.drop_duplicates(subset='A', inplace=True)

df11=df.reset_index(drop=True)
print(df11)
'''

import pandas as pd

# 假设df是你的DataFrame
df = pd.DataFrame({
    'column1': [1, 1, 3,3, 4, 5],
    'column2': [1, 1, 2,4, 4, 4],
    'column3': [1, 1, 1,5, 5, 4],
    'column4': [1, 1, 0,6, 4, 4],
})

# 筛选出column1和column2值不同的所有行
# 列出所有需要比较的列对
# unique_rows = df.drop_duplicates(keep=False)
date =df[df['column1'].isin([3])]
print(date)
print(date['column1'].tolist()[0])
# column_pairs = [('column1', 'column2'), ('column3', 'column4')]  # 可以根据需要添加更多列对
#
# # 使用列表解析来创建条件
# conditions = [df[col1] != df[col2] for col1, col2 in column_pairs]
# print(conditions)
# # 使用any函数来检查任何条件是否为True
# mask = pd.concat(conditions, axis=1).any(axis=1)
#
# # 使用布尔mask来筛选行
# filtered_df = df[mask]
#
# print(filtered_df)

# print(datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
# print(datetime.datetime.now().strftime("%Y-%m-%d"))
# 征收项目='职工基本养老保险(个人缴纳)_2024-03至2024-03_预处理信息明细.xls'
# print(征收项目[0:征收项目.index('_')])
# i=0
# while i<10:
#     if i ==5 :
#         i= i-1
#         print('返回循环'+str(i))
#         continue
#     print('执行中'+str(i))
#     i+=1

##获取本月的最后一天
from datetime import datetime
from dateutil.relativedelta import relativedelta

def add_year_to_date_string(date_string, format='%Y-%m-%d'):
    # 将字符串转换为datetime对象
    date_object = datetime.strptime(date_string, format)
    # 使用relativedelta增加一年
    new_date = date_object + relativedelta(years=1)
    # 将datetime对象转换回字符串
    return new_date.strftime(format)

# 示例使用
date_string = '2023-05-28'  # 闰年的2月29日
new_date_string = add_year_to_date_string(date_string)
print(new_date_string)  # 输出应为 '2024-02-29'（如果2024年也是闰年）


my_list = [''] * 50
print(my_list)

# 定义一个字典
my_dict = {'a': 1, 'b': 2, 'c': 3}

# 将字典转换为元组
my_tuple = tuple(my_dict.items())

print(my_tuple)
print(my_tuple)

str1='深圳'
print(str1.split('-')[0])


# 创建一个示例DataFrame
df = pd.DataFrame({
    'A': ['foo', 'bar', 'foo', 'bar', 'foo', 'bar'],
    'B': ['one', 'one', 'two', 'three', 'two', 'two'],
    'C': [1, 5, 5, 2, 5, 5],
    'D': [2.0, 5., 8., 1., 2., 9.]
})
print(df)
# 按照列'A'进行分组
grouped = df.groupby('A')

# 输出每个组的统计信息
for name, group in grouped:
    print(f"Group name: {name}")
    print(group)

str1='0A 采购订单 5400169247 已创建'
print(str1[str1.index('采购订单 ',)+5:str1.index(' 已创建')])

#判断其中是否有非空的元素，全空为False，不为全空为True
date_stri=['']
date_stri1=[[''],['']]
print(any(date_stri))
print(any(date_stri1))

print(datetime.now().strftime("%Y-%m-%d"))



import pandas as pd

# # 假设 df1 和 df2 是您的两个 DataFrame，它们都有一个共同的列 'key'
# df1 = pd.DataFrame({'key': ['A', 'B', 'C', 'D'], 'value1': [1, 2, 3, 4]})
# df2 = pd.DataFrame({'key': ['B', 'C', 'E'], 'value2': [5, 6, 7]})
#
# # 使用 merge 方法进行左连接，并指示 how='left'
# merged = pd.merge(df1, df2, on='key', how='left')
# print(merged)
# # 删除右侧 DataFrame 列有缺失值的行，这些行在 df2 中没有匹配项
# df1_unique = merged.dropna(subset=['value2'])
#
# merged = pd.merge(df1, df2, on=['A', 'B'], how='left', indicator=True)
#
# # 筛选只存在于df1中的行
# df1_unique = merged[merged['_merge'] == 'left_only'].drop(columns=['_merge'])
# # 如果需要，删除用于合并的辅助列
# df1_unique = df1_unique.drop(columns=['value2'])

# print(df1_unique)
'''
import pandas as pd
import numpy as np

# 示例数据
data = {
    'A': [1, 1, 2, 3, 3, 3],
    'B': [4, 4, 5, np.nan, 6, 6],
    'C': ['x', 'x', 'y', 'z', 'z', 'w']
}
df = pd.DataFrame(data)

# 使用groupby和cumcount来标记每一组内的连续行
df['row_num'] = df.groupby(df.columns.tolist(), as_index=False).cumcount()
print(df)
# 排除包含空值的行（这里假设NaN在任何列中都是空的）
# df = df.dropna(subset=df.columns.tolist())
df = df.dropna(subset=['B'])

# 保留每组的最后一行（即cumcount为0的行）
df = df[df['row_num'] == 0].drop(columns='row_num')

print(df)

import pandas as pd

# 创建一个DataFrame
df = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6]})

# 假设我们想在索引1之前插入新行，索引为0.5
df.loc[0.5] = [10, 20]  # 新行的数据
df = df.sort_index()  # 重新排序索引
de=df.reset_index(drop=True) #重置索引
print(de)'''
def ces1():
    import pandas as pd
    print('开始测试1')
    # 示例数据
    df1 = pd.DataFrame({'A': ['（1', '2)', '3('],'C': ['0','9','8']})
    df2 = pd.DataFrame({'B': ['1)', '2（', '3)'],'C': ['0','9','8']})

    # 预处理：将括号替换为下划线
    df1['A'] = df1['A'].str.replace('[()（）]', '_', regex=True)
    df2['B'] = df2['B'].str.replace('[()（）]', '_', regex=True)

    # 合并数据
    result = pd.merge(df1, df2, on='C')

    print(result)

ces1()


import openpyxl
from openpyxl import load_workbook

# 打开Excel文件
workbook = openpyxl.load_workbook('../文件/下载异动人员报表2024-04-26.xlsx')
# 获取所有sheet的名称
sheet_names = workbook.sheetnames
print(sheet_names)